package cn.edu.szh.dao;

import cn.edu.szh.bean.ScoreInfo;
import cn.edu.szh.bean.ScoreInputInfo;
import cn.edu.szh.bean.StudentInfo;
import cn.edu.szh.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScoreDao {
    public static List<ScoreInfo> getAll() {
        List<ScoreInfo> list = new ArrayList<>();
        //查询数据库，返回用户信息
        Connection conn = DBUtil.getConnection();
        String sql = "select * from score_info";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                ScoreInfo scoreInfo = new ScoreInfo();
                scoreInfo.setGrade_id(rs.getInt("grade_id"));
                scoreInfo.setClass_id(rs.getInt("class_id"));
                scoreInfo.setStudent_id(rs.getInt("student_id"));
                scoreInfo.setName(rs.getString("name"));
                scoreInfo.setSubject_id(rs.getInt("subject_id"));
                scoreInfo.setSname(rs.getString("sname"));
                scoreInfo.setScore(rs.getDouble("score"));
                scoreInfo.setTerm(rs.getString("term"));
                list.add(scoreInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("查询分数失败");
        }
        DBUtil.closeConnection(conn);
        return list;
    }

    public static boolean add(ScoreInfo scoreInfo) {
        Connection conn = DBUtil.getConnection();
        String  sql = "insert into score_info values(?,?,?,?,?,?)";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,scoreInfo.getStudent_id());
            ps.setString(2,scoreInfo.getName());
            ps.setInt(3,scoreInfo.getSubject_id());
            ps.setString(4,scoreInfo.getSname());
            ps.setDouble(5,scoreInfo.getScore());
            ps.setString(6,scoreInfo.getTerm());
            ps.executeUpdate();
            return true;
        }
        catch (SQLException e){
            e.printStackTrace();
            System.out.println("添加失败");
            return false;
        }finally {
            DBUtil.closeConnection(conn);
        }
    }

    public boolean update(ScoreInfo scoreInfo) {
        Connection conn = DBUtil.getConnection();
        String sql = "update score_info set student_id=?,name=?,subject_id=?,sname=?,score=?,term=? where student_id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,scoreInfo.getStudent_id());
            ps.setString(2,scoreInfo.getName());
            ps.setInt(3,scoreInfo.getSubject_id());
            ps.setString(4,scoreInfo.getSname());
            ps.setDouble(5,scoreInfo.getScore());
            ps.setString(6,scoreInfo.getTerm());
            ps.setInt(7,scoreInfo.getStudent_id());
            ps.executeUpdate();
            return true;
        }
        catch (SQLException e) {
            e.printStackTrace();
            System.out.println("添加用户失败");
            return false;
        }
    }

    public boolean delete(int studentId) {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from score_info where student_id = ?";
        try{
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,studentId);
            int row= ps.executeUpdate();
            // int row1= ps.executeUpdate();
            if(row==1){
                return true;
            }else{
                System.out.println("不存在学号为"+studentId+"的学生");
                return false;
            }
        }catch (SQLException e){
            e.printStackTrace();
            System.out.println("删除失败");
            return false;
        }finally {
            DBUtil.closeConnection(conn);
        }

    }
    public List<ScoreInfo> getByGradeIdAndClassIdAndSubjectId(int gradeId, int classId, int subjectId) {
        ArrayList<ScoreInfo> result = new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        try {
            //conn = DBUtil.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from score_info where grade_id=? and class_id=? and subject_id=?");
            ps.setInt(1, gradeId);
            ps.setInt(2, classId);
            ps.setInt(3, subjectId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ScoreInfo infoTemp = new ScoreInfo();
                infoTemp.setGrade_id(rs.getInt("grade_id"));
                infoTemp.setClass_id(rs.getInt("class_id"));
                infoTemp.setStudent_id(rs.getInt("student_id"));
                infoTemp.setName(rs.getString("name"));
                infoTemp.setSubject_id(rs.getInt("subject_id"));
                infoTemp.setSname(rs.getString("sname"));
                infoTemp.setScore(rs.getDouble("score"));
                infoTemp.setTerm(rs.getString("term"));
                result.add(infoTemp);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询用户失败");
        }
        return result;
    }
}


